In my last post I described issues that might stop your migration to a SQL Server Managed Instance (SQL MI). This covers configuration items that differ or are not supported in SQL MI. These likely won’t stop your migration, but they could slow you down if you aren’t ready for these changes.

As with previous issues discussed, testing your migration is key. Validate all of your settings and be prepared to make some changes during your migration process. Most of the incompatible options make sense when you think about the purpose of SQL MI – it is controlled by Microsoft. Hardware settings, local file access, high-availability settings, and auditing are configured differently or completely disabled.

Server Configuration

Since SQL MI is largely maintained by Microsoft, some server level configuration items are not available. I ran into a few of these during our migration. I did a quick test to find others that don’t support user changes. As a reminder, don’t change the default settings unless you have a specific reason. If you make any changes from the defaults, they need to be tested. It is very possible to hurt performance if you change the default settings.

SP_CONFIGURE

Many server settings are controlled with sp_configure. As with an on-prem instance, advanced options still need to be enabled in SQL MI. Run the following commands to see and enable configuration of all compatible server options.

The following options throw errors when trying to configure them on SQL MI. Some of the error messages differ, but regardless of the exact reason, they are not configurable on SQL MI. Some of the options that failed are listed in the documentation as compatible with SQL MI, which emphasizes the need for testing.

It’s interesting to note that the failed option list expanded by one during my validation and testing over a one-week period. The option, “max UCS send boxcars” was not present during my first test. It showed up during final validation before publishing, so expect further changes and differences to compatible options in the future.

I used the following script to validate each option. It attempts to set the value of each option to the currently configured value so no actual changes are made – it just attempts to access each option.

Server Configuration

Additional options can be set on SQL Server using ALTER SERVER CONFIGURATION.

Documentation only lists SQL Server, not SQL MI for this command. I tested a few options to be sure and the results were as expected. Since these options largely deal with physical configurations, such as CPU affinity, they wouldn’t make sense in SQL MI.

Tempdb

Tempdb options are very limited. The drive locations and number of files are controlled by Microsoft. In my, somewhat limited, testing it has not been an issue. Best practices are followed regarding the number of files and I expect any tempdb configuration to be updated as recommendations change over time.

I mentioned in a previous post that you can’t change the server configuration option, ‘tempdb metadata memory-optimized’, and that was also shown above. After testing and talking to our Microsoft technical contact, this is not an issue. The database engine has been updated to incorporate the option. In short, the latest version of the engine is more efficient. Tempdb metadata operations don’t stress the engine as in previous versions. I verified this using OStress (part of the RML tools) and was unable to cause latch waits by creating and dropping temp tables.

I used 100 threads for the stress test. Each thread created and dropped 10,000 temp tables. The entire process took about 45 minutes to finish and didn’t create a noticeable load on the server. That includes CPU, latch waits, and any adverse impact to other queries. The same test with an on-prem system, not configured with the tempdb metadata option, created noticeable load and adversely impacted the system. The load in SQL MI was similar to an on-prem system configured to use tempdb data in memory.

Database Scoped Configuration

Database scoped configurations are set using the ALTER DATABASE SCOPED CONFIGURATION command.

I used a script similar to the previous server script to verify database scoped configurations. All options were allowed. As with server configurations, leave these at the default unless you have a specific reason to change them and test any changes you do make. Current settings can be viewed with the following DMV. Configurations are specific to each database.

Database Options

Database options are set with the ALTER DATABASE command.

Like database scoped configurations, database options are set at a database level. Each database can have different settings based on the application need. Refer to the Microsoft documentation on supported options and test any that you use in your environment. Many of the options are not supported in SQL MI. There are too many to list, but the highlights follow. Note that these are listed as <option spec> (categories) and usually cover multiple options. Some option specs that are supported in SQL MI also have individual options not supported (e.g., AUTO_CLOSE). The following option specs are not supported in SQL MI:

This is a side-by-side comparison of the documentation for SQL Server and SQL MI. You can see the large number of unsupported option specs.

Summary

Check any server and database configurations during your SQL MI testing. There are many server and database level options that aren’t supported. If you have automated processes to restore or setup environments, be sure to account for the new changes. You’ll also to be ready to update options over time, as my testing revealed ongoing changes.

References